﻿using AutoMapper;
using ClosedXML.Excel;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using NLog.Fluent;
using SHRIS.MIS.Common.Util.Model;
using SHRIS.MIS.Entity.ScadaData;
using SHRIS.MIS.IService.APIExternal;
using SHRIS.MIS.Model.Param.APIExternal;
using SHRIS.MIS.Service.Device;
using SHRIS.MIS.Service.LinePlantService;
using SHRIS.MIS.Web.Controllers;
using SHRIS.MIS.Web.Filter;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Threading.Tasks;

namespace SHRIS.MIS.Web.Areas.ReportManage.Controllers
{
    [Area("ReportManage")]
    public class ReportApiController : BaseController
    {
        #region 服务注入
        private readonly IMapper _mapper;
        LinePlantService _LinePlantService;
        IExcelRowPositionRecordService _excelRowPositionRecordService;
        IDeviceStatusService _deviceStatusService;
        IThreeCoordinatesMeasureMoudleService _threeCoordinatesMeasureMoudleService;
        IPassInformationService _passInformationService;
        IThreedCoordinateService _threedCoordinateService;
        IProductionService _productService;
        IDeviceStatusInfoRecordService _deviceStatusInfoRecordService;
        public ReportApiController(IMapper mapper, LinePlantService LinePlantService, IExcelRowPositionRecordService excelRowPositionRecordService, 
            IThreeCoordinatesMeasureMoudleService threeCoordinatesMeasureMoudleService, IDeviceStatusService deviceStatusService, 
            IPassInformationService passInformationService,IThreedCoordinateService threedCoordinateService,
            IProductionService productService, IDeviceStatusInfoRecordService deviceStatusInfoRecordService)
        {
            _mapper = mapper;
            _LinePlantService = LinePlantService;
            _excelRowPositionRecordService = excelRowPositionRecordService;
            _deviceStatusService = deviceStatusService;
            _threeCoordinatesMeasureMoudleService = threeCoordinatesMeasureMoudleService;
            _passInformationService = passInformationService;
            _threedCoordinateService = threedCoordinateService;
            _productService = productService;
            _deviceStatusInfoRecordService = deviceStatusInfoRecordService;
        }
        #endregion

        #region 视图功能

        [AuthorizeFilter("report:preview")]
        public IActionResult MesThreedCoordinates()
        {
            return View();
        }

        [AuthorizeFilter("report:preview")]
        public IActionResult MachineEff()
        {
            return View();
        }

        [AuthorizeFilter("report:preview")]
        public IActionResult OEE()
        {
            return View();
        }

        [AuthorizeFilter("report:preview")]
        public IActionResult MaterielTrack()
        {
            return View();
        }

        [AuthorizeFilter("report:preview")]
        public IActionResult ThreedCoordinates()
        {
            return View();
        }
        #endregion

        #region API

        /// <summary>
        /// 获取产品批次
        /// </summary>
        /// <returns>excel行数记录对象</returns>
        [HttpGet]
        public async Task<IActionResult> getProductBatch()
        {           
            IEnumerable<ExcelRowPositionRecordEntity> result = await _excelRowPositionRecordService.SearchAllExcelRowPositionRecord();
            var obj = new
            {
                result= result
            };
            return Json(obj);
        }

        /// <summary>
        /// 获取三坐标结果
        /// </summary>
        /// <param name="productBatch"></param>
        /// <param name="pageSize"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        public async Task<IActionResult> getInfoRecord(string productBatch, int pageSize, int limit)
        {
            Pagination p = new Pagination();
            p.PageIndex = pageSize;
            p.PageSize = limit;
            IEnumerable<ThreeCoordinatesMeasureMoudleEntity> result= await _threeCoordinatesMeasureMoudleService.SearchThreeCoordinatesMeasureMoudleByPrudctBatch(productBatch, p);
            var obj = new
            {
                result = result,
                total = p.TotalCount
            };
            return Json(obj);
        }

        /// <summary>
        /// 生成MES模板excel
        /// </summary>
        /// <param name="targetDer">生成目标目录</param>
        /// <param name="productBatch">批次</param>
        /// <param name="productCode"></param>
        /// <param name="productName"></param>
        /// <param name="workCode"></param>
        /// <param name="workName"></param>
        /// <param name="InsertWorkCode"></param>
        /// <param name="InsertWorkName"></param>
        /// <returns></returns>
        public async Task<IActionResult> createMesExcel(string targetDer,string productBatch,string productCode,string productName,string workCode,string workName,string InsertWorkCode,string InsertWorkName)
        {
            bool result = true;
            try
            {
                // 批次文件存放的地址
                string modulePath = "C:\\Users\\42199\\Desktop\\工作资料\\西北新项目\\整理分析\\待开发功能\\excel模板\\生成目标目录";

                // 批次文件地址
                string sourceFile = modulePath + "\\" + productBatch + ".xlsx";

                // 目标地址
                string targetName = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + " " +
                    DateTime.Now.Hour.ToString() + "." + DateTime.Now.Minute.ToString() + "." + DateTime.Now.Second.ToString()
                    + "_" + productBatch + ".xlsx";
                string targetFile = targetDer + "\\" + targetName;

                // 创建
                System.IO.File.Copy(sourceFile, targetFile);

                // 填入头部基本信息
                XLWorkbook xLWorkbook = new XLWorkbook(targetFile);
                IXLWorksheet worksheet = xLWorkbook.Worksheets.Worksheet("Sheet1");
                // 产品代号
                worksheet.Cell(1, 2).Value = productCode;
                // 产品名称
                worksheet.Cell(2, 2).Value = productName;
                // 工序号
                worksheet.Cell(3, 2).Value = workCode;
                // 工序名称
                worksheet.Cell(4, 2).Value = workName;
                // 产线内工序号
                worksheet.Cell(5, 2).Value = InsertWorkCode;
                // 产线内工序名称
                worksheet.Cell(6, 2).Value = InsertWorkName;
                using (MemoryStream stream = new MemoryStream())
                {
                    xLWorkbook.SaveAs(targetFile);
                }
            }
            catch(Exception ex)
            {
                result = false;
            }
            var obj = new
            {
                result = result
            };
            return Json(obj);
        }

        /// <summary>
        /// 获取设备历史信息
        /// </summary>
        /// <param name="deviceCode">设备编码</param>
        /// <param name="stime">开始时间</param>
        /// <param name="etime">结束时间</param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetDeviceHistory(string deviceCode, string stime, string etime)
        {
            List<DeviceStatusEntity> _result = new List<DeviceStatusEntity>();
            string _stime = stime + " 00:00:00.000 +08:00";
            string _etime = etime + " 23:59:59.999 +08:00";
            DateTimeOffset s = DateTimeOffset.Parse(_stime);
            DateTimeOffset e = DateTimeOffset.Parse(_etime);
            // 找出记录起始点最近一条记录
            string sql = "select Top 1 * from tb_deviceStatus where DeviceCode='" + deviceCode + "'and OccurTime<='" + s + "' order by OccurTime desc";
            string _sql = "select Top 1 * from tb_deviceStatus where DeviceCode='" + deviceCode + "'and OccurTime>='" + e + "' order by OccurTime";
            var beforeLast = await _deviceStatusService.SearchBeforeRecordLast(sql);
            var recordResult = await _deviceStatusService.SearchDeviceStatus(deviceCode, s, e);
            var afterLast = await _deviceStatusService.SearchBeforeRecordLast(_sql);

            foreach (DeviceStatusEntity d in beforeLast)
            {
                _result.Add(d);
            }
            foreach (DeviceStatusEntity d in recordResult)
            {
                _result.Add(d);
            }
            foreach (DeviceStatusEntity d in afterLast)
            {
                _result.Add(d);
            }
            List<DeviceStatusEntity> result = Sort(_result);
            var obj = new
            {
                result = result
            };
            return Json(obj);
        }

        /// <summary>
        /// 获取零件结果追踪
        /// </summary>
        /// <param name="likeMCode"></param>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> getMaterielTrack(string likeMCode, string stime, string etime, int pageSize = 1, int limit = 5)
        {

            List<PassInformationEntity> result = new List<PassInformationEntity>();
            int total = 0;
            string message = null;
            string _stime = stime + " 00:00:00.000 +08:00";
            string _etime = etime + " 23:59:59.999 +08:00";
            DateTimeOffset s = DateTimeOffset.Parse(_stime);
            DateTimeOffset e = DateTimeOffset.Parse(_etime);

            try
            {
                // 1. 模糊匹配的物料码
                string sql1 = "select ProductionId,MAX(UpdateTime) as UpdateTime from tb_passInformation " +
                    "where ProductionId like '%" + likeMCode + "%'and (UpdateTime between '" + s + "' and '" + e + "') " +
                    "group by ProductionId order by UpdateTime desc";
                IEnumerable<PassInformationEntity> result1 = await _passInformationService.likeMCode(sql1);
                foreach (PassInformationEntity entity1 in result1)
                {
                    string sql2 = "select RecordId,ProductionId,DeviceCode,UpdateTime,Program,ActionMode from tb_passInformation " +
                        "where ProductionId='" + entity1.ProductionId + "' order by UpdateTime desc";
                    IEnumerable<PassInformationEntity> result2 = await _passInformationService.likeMCode(sql2);
                    result.Add(result2.First());
                }
                total = result.Count;
                // 2. 截取pageSize、limit
                List<PassInformationEntity> _result = new List<PassInformationEntity>();
                int start = (pageSize - 1) * limit;
                int end = pageSize * limit;
                for (int i = 0; i < result.Count; i++)
                {
                    if (i >= start && i < end)
                    {
                        _result.Add(result[i]);
                    }
                }
                result = _result;
            }
            catch (Exception ex)
            {
                message = ex.Message;
            }

            var obj = new
            {
                result = result,
                total = total,
                message = message
            };
            return Json(obj);
        }

        /// <summary>
        /// 获取零件URL
        /// </summary>
        /// <param name="materielCode"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> getDetailMCode(string materielCode)
        {
            bool result = true;
            string message = "";
            List<PassInformationEntity> data = null;
            try
            {
                IEnumerable<PassInformationEntity> _data = await _passInformationService.SearchPassInformationById(materielCode);
                data = SortRecord(_data);
            }
            catch (Exception ex)
            {
                result = false;
                message = ex.Message;
            }

            var obj = new
            {
                result = result,
                data = data,
                message = message
            };
            return Json(obj);
        }

        /// <summary>
        /// 获取三坐标测量结果
        /// </summary>
        /// <param name="stime"></param>
        /// <param name="etime"></param>
        /// <param name="pageSize"></param>
        /// <param name="limit"></param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> GetThreedCoordinates(string stime, string etime, int pageSize, int limit)
        {
            Pagination p = new Pagination();
            p.Sort = "OccurTime";
            p.PageIndex = pageSize;
            p.PageSize = limit;
            string _stime = stime + " 00:00:00.000 +08:00";
            string _etime = etime + " 23:59:59.999 +08:00";
            DateTimeOffset s = DateTimeOffset.Parse(_stime);
            DateTimeOffset e = DateTimeOffset.Parse(_etime);

            var result = await _threedCoordinateService.SearchBytime(s, e, p);
            var obj = new
            {
                result = result,
                total = p.TotalCount
            };

            return Json(obj);
        }

        /// <summary>
        /// 创建三坐标结果excel
        /// </summary>
        /// <param name="group">公司名称</param>
        /// <param name="currentUser">操作员</param>
        /// <param name="date">日期</param>
        /// <param name="time">时间</param>
        /// <param name="filePath">要保存excel的路径</param>
        /// <param name="recordId">三坐标结果的ID</param>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> createThreedCoordinatesExcel(string group, string currentUser, string date, string time, string filePath, string recordId)
        {
            bool result = true;
            string message = "";

            try
            {
                Guid id = new Guid(recordId);
                IEnumerable<ThreeCoordinatesMeasureRecordEntity> info = await _LinePlantService.SearchById(id);
                ThreeCoordinatesMeasureRecordEntity entity = info.First();

                string mCode = entity.MCode;
                string handlMachine = entity.HandleMachine;
                List<string[]> list = new List<string[]>();
                string[] totalData = entity.Result.Split("/");
                for (int i = 0; i < totalData.Length; i++)
                {
                    /**
                     * 结果数组
                     * 实际   误差  趋势  结果
                     */
                    string[] resultData = new string[4];
                    string[] detailData = totalData[i].Split(":");
                    resultData[0] = detailData[1];
                    resultData[1] = detailData[3];
                    resultData[2] = detailData[2];
                    resultData[3] = "未知";
                    if (detailData[4] == "406")
                    {
                        resultData[3] = "不合格";
                    }
                    if (detailData[4] == "336")
                    {
                        resultData[3] = "合格";
                    }
                    list.Add(resultData);
                }

                /**
                 * 生成excel方法
                 * 从模板excel文件中copy一份到目标目录下
                 * 打开刚创建的excel，往里面填值
                 */

                // 1 创建目标文件
                string filename = DateTime.Now.Year.ToString() + "-" + DateTime.Now.Month.ToString() + "-" + DateTime.Now.Day.ToString() + "_" + mCode + ".xlsx";
                string file = filePath + "\\" + filename;
                System.IO.File.Copy("C:\\Users\\42199\\Desktop\\工作资料\\西北新项目\\整理分析\\待开发功能\\excel模板\\三坐标模板.xlsx", file);

                // 2 写入数据
                if (System.IO.File.Exists(file))
                {
                    XLWorkbook xLWorkbook = new XLWorkbook(file);
                    IXLWorksheet worksheet = xLWorkbook.Worksheets.Worksheet("Sheet1");
                    // 公司
                    worksheet.Cell(2, 2).Value = group;
                    // 操作员
                    worksheet.Cell(3, 2).Value = currentUser;
                    // 日期
                    worksheet.Cell(4, 2).Value = date;
                    // 时间
                    worksheet.Cell(5, 2).Value = time;
                    // 物料码
                    worksheet.Cell(6, 2).Value = mCode;
                    // 加工机床
                    worksheet.Cell(7, 2).Value = handlMachine;

                    // 表格数据
                    int startRow = 10;
                    for (int i = 0; i < list.Count; i++)
                    {
                        worksheet.Cell(startRow, 4).Value = list[i][0];
                        worksheet.Cell(startRow, 5).Value = list[i][1];
                        worksheet.Cell(startRow, 8).Value = list[i][2];
                        worksheet.Cell(startRow, 9).Value = list[i][3];
                        startRow++;
                    }
                    using (MemoryStream stream = new MemoryStream())
                    {
                        xLWorkbook.SaveAs(file);
                    }
                }
            }
            catch (Exception ex)
            {
                result = false;
                message = ex.Message;
            }

            var obj = new
            {
                result = result,
                message = message
            };
            return Json(obj);
        }

        /// <summary>
        /// 响应设备OEE页面的查询请求
        /// </summary>
        /// <returns></returns>
        [HttpGet]
        public async Task<IActionResult> resOeeReq(string deviceCode, string stime, string etime)
        {
            List<DeviceStatusEntity> _result = new List<DeviceStatusEntity>();
            string _stime = stime + " 00:00:00.000 +08:00";
            string _etime = etime + " 23:23:59.999 +08:00";
            DateTimeOffset s = DateTimeOffset.Parse(_stime);
            DateTimeOffset e = DateTimeOffset.Parse(_etime);
            // 找出记录起始点最近一条记录
            string sql = "select Top 1 * from tb_deviceStatus where DeviceCode='" + deviceCode + "'and OccurTime<='" + s + "' order by OccurTime desc";
            string _sql = "select Top 1 * from tb_deviceStatus where DeviceCode='" + deviceCode + "'and OccurTime>='" + e + "' order by OccurTime";
            var beforeLast = await _deviceStatusService.SearchBeforeRecordLast(sql);
            var recordResult = await _deviceStatusService.SearchDeviceStatus(deviceCode, s, e);
            var afterLast = await _deviceStatusService.SearchBeforeRecordLast(_sql);

            foreach (DeviceStatusEntity d in beforeLast)
            {
                _result.Add(d);
            }
            foreach (DeviceStatusEntity d in recordResult)
            {
                _result.Add(d);
            }
            foreach (DeviceStatusEntity d in afterLast)
            {
                _result.Add(d);
            }
            List<DeviceStatusEntity> result = Sort(_result);

            /**
             * 状态时间合
             * 0 中断
             * 1 停止
             * 2 加工
             * 3 等待
             * 4 报警
             */
            long[] timeDistribution = new long[5];
            // 总时间
            long timeTotal = 0;
            // 总的故障次数
            int recordTotal = 0;
            for(int i = 0; i < result.Count; i++)
            {
                if ((i + 1) == result.Count)
                {
                    break;
                }
                long start = (result[i].OccurTime.LocalDateTime.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
                long end = (result[i+1].OccurTime.LocalDateTime.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
                long _total = end - start;
                timeDistribution[result[i].StatusCode - 1] += (_total)/60;
                timeTotal += _total;
                if (result[i].StatusCode == 5|| result[i].StatusCode==1)
                {
                    recordTotal++;
                }
            }
            timeTotal = timeTotal / 60;

            IEnumerable<DeviceStatusInfoRecordEntity> _record = await _deviceStatusInfoRecordService.SearchRecord(deviceCode, s, e);
            List<DeviceStatusInfoRecordEntity> records = SortRecord(_record);
            /**
             * 各个故障的填写次数
             * 0 计划内
             * 1 计划外
             * 2 程序因素
             * 3 其它
             */
            int[] recordNum = new int[4];
            for(int i = 0; i < records.Count; i++)
            {
                switch (records[i].Reason_1)
                {
                    case "计划内":
                        {
                            recordNum[0] += 1;
                            break;
                        }
                    case "计划外":
                        {
                            recordNum[1] += 1;
                            break;
                        }
                    case "程序因素":
                        {
                            recordNum[2] += 1;
                            break;
                        }
                    case "其它":
                        {
                            recordNum[3] += 1;
                            break;
                        }
                }
            }
            var obj = new
            {
                timeDistribution= timeDistribution,
                timeTotal= timeTotal,
                recordNum= recordNum,
                recordTotal= recordTotal
            };
            return Json(obj);
        }
        #endregion

        #region 辅助方法

        /// <summary>
        /// 对得到的数据进行排序
        /// </summary>
        /// <param name="data"></param>
        /// <returns></returns>
        public List<DeviceStatusEntity> Sort(List<DeviceStatusEntity> data)
        {
            if (data.Count == 0)
            {
                return data;
            }
            else
            {
                for (int i = 0; i < data.Count; i++)
                {
                    for (int j = 0; j < data.Count - i - 1; j++)
                    {
                        if (data[j].OccurTime > data[j + 1].OccurTime)
                        {
                            DeviceStatusEntity transfer = data[j];
                            data[j] = data[j + 1];
                            data[j + 1] = transfer;
                        }
                    }
                }
                return data;
            }
        }

        public List<PassInformationEntity> SortRecord(IEnumerable<PassInformationEntity> data)
        {
            List<PassInformationEntity> result = new List<PassInformationEntity>();
            if (data.Count() != 0)
            {
                foreach (PassInformationEntity d in data)
                {
                    result.Add(d);
                }
                for (int i = 0; i < result.Count; i++)
                {
                    for (int j = 0; j < result.Count - i - 1; j++)
                    {
                        if (result[j].UpdateTime > result[j + 1].UpdateTime)
                        {
                            PassInformationEntity transfer = result[j];
                            result[j] = result[j + 1];
                            result[j + 1] = transfer;
                        }
                    }
                }
            }
            return result;
        }

        public List<DeviceStatusInfoRecordEntity> SortRecord(IEnumerable<DeviceStatusInfoRecordEntity> data)
        {
            List<DeviceStatusInfoRecordEntity> result = new List<DeviceStatusInfoRecordEntity>();
            if (data.Count() != 0)
            {
                foreach (DeviceStatusInfoRecordEntity d in data)
                {
                    result.Add(d);
                }
                for (int i = 0; i < result.Count; i++)
                {
                    for (int j = 0; j < result.Count - i - 1; j++)
                    {
                        if (result[j].RecordTime > result[j + 1].RecordTime)
                        {
                            DeviceStatusInfoRecordEntity transfer = result[j];
                            result[j] = result[j + 1];
                            result[j + 1] = transfer;
                        }
                    }
                }
            }
            return result;
        }

        #endregion
    }
}
